package com.core.excel;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * excel基础工具类
 */
@Slf4j
public class ExcelKit {


    /**
     * 解析上传Excel文件
     *
     * @param filePath
     * @return
     * @throws IOException
     */
    public static Map<Integer, List<Map<String, String>>> analysis(String filePath, Integer... sheets) throws Exception {
        File file = new File(filePath);
        Workbook workbook = assemblyWorkbook(file.getName(), new FileInputStream(file));
        return analysisWorkbookMap(workbook, sheets);
    }

    /**
     * 组装成excel工作对象
     *
     * @param filename
     * @param inputStream
     * @return
     * @throws Exception
     */
    private static Workbook assemblyWorkbook(String filename, InputStream inputStream) throws Exception {
        //获取workbook对象
        Workbook workbook = null;
        //根据后缀名是否excel文件 2003
        if (filename.endsWith("xls")) {
            workbook = new HSSFWorkbook(inputStream);
        }
        // 2007
        else if (filename.endsWith("xlsx")) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
     * 解析Excel文件薄
     *
     * @param workbook
     * @return
     * @throws IOException
     */
    private static Map<Integer, List<Map<String, String>>> analysisWorkbookMap(Workbook workbook, Integer... sheets) throws Exception {
        if (sheets == null || sheets.length == 0) {
            sheets = new Integer[1];
            sheets[0] = 0;
        }
        if (workbook == null) {
            return Collections.emptyMap();
        }
        FormulaEvaluator formulaEval = workbook.getCreationHelper().createFormulaEvaluator();
        Map<Integer, List<Map<String, String>>> excelDataMap = new HashMap<>();
        List<Map<String, String>> sheetDataList;
        Map<String, String> line;
        List<String> titleList;
        int firstRowNum;
        int lastRowNum;
        Row rowNo1;
        Row row;
        Sheet sheet;
        String cellValue;
        Cell cell;
        for (Integer sheetNum : sheets) {
            //获取第一个sheet
            sheet = workbook.getSheetAt(sheetNum);
            if (sheet == null) {
                log.error("sheet idx:" + sheetNum + "不存在");
                continue;
            }
            //创建对象，把每一行作为一个String数组，所以数组存到集合中
            sheetDataList = new ArrayList<>();
            //获取当前sheet开始行和结束行
            firstRowNum = sheet.getFirstRowNum();
            lastRowNum = sheet.getLastRowNum();
            rowNo1 = sheet.getRow(firstRowNum);
            //获取当前行的开始列和结束列
            short firstCellNum = rowNo1.getFirstCellNum();
            short lastCellNum = rowNo1.getLastCellNum();
            int lastCellNum2 = rowNo1.getPhysicalNumberOfCells();
            if (lastCellNum2 == 0) {
                continue;
            }
            //取得各个列的字段名称
            titleList = new ArrayList<>();
            for (int cellNum = firstCellNum; cellNum < lastCellNum; cellNum++) {
                cell = rowNo1.getCell(cellNum);
                cellValue = getCellValue(cell, formulaEval);
                cellValue = cellValue.replace("\r", "");
                cellValue = cellValue.replace("\n", "");
                cellValue = cellValue.replace("\b", "");
                cellValue = cellValue.trim();
                if(cellValue.startsWith("*")){
                    cellValue = cellValue.substring(1);
                    cellValue = cellValue.trim();
                }
                titleList.add(cellValue);
                //取得批注
//                Comment comment = cell.getCellComment();
//                if (comment != null) {
//                    System.out.println(comment.getString().getString());
//                }
            }
            //循环开始，除了前两行
            for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
                //获取当前行
                row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                //循环所有行
                line = new HashMap<>();
                for (int i = 0; i < titleList.size(); i++) {
                    cellValue = getCellValue(row.getCell(i), formulaEval);
                    line.put(titleList.get(i), cellValue);
                }
                sheetDataList.add(line);

            }
            excelDataMap.put(sheetNum, sheetDataList);
        }
        return excelDataMap;
    }


    //把每一个cell转换为string
    public static String getCellValue(Cell cell, FormulaEvaluator formulaEval) {
        if (cell == null || "".equals(cell)) {
            return "";
        }
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        DataFormatter formatter = new DataFormatter();
        //判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: //数字0
                if (DateUtil.isCellDateFormatted(cell)) {
                    short format = cell.getCellStyle().getDataFormat();
                    SimpleDateFormat sdf = null;
                    if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    } else if (format == 14 || format == 31 || format == 57 || format == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        cellValue = sdf.format(date);
                    } else {// 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    try {
                        cellValue = sdf.format(cell.getDateCellValue());// 日期
                    } catch (Exception e) {
                        try {
                            throw new Exception("exception on get date data !".concat(e.toString()));
                        } catch (Exception e1) {
                            e1.printStackTrace();
                        }
                    } finally {
                        sdf = null;
                    }
                } else {
                    cellValue = formatter.formatCellValue(cell);
                }
                break;
            case STRING: //字符串1
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: //公式
                try {
                    cellValue = formatter.formatCellValue(cell, formulaEval);
                } catch (Exception ex) {
                    cellValue = String.valueOf(cell.getRichStringCellValue());
                }
                break;
            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }


}
